package com.red.wood.module.questionnaire.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.red.wood.common.domain.Page;
import com.red.wood.common.springjdbc.CommonDao;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.module.questionnaire.dao.QuestionnaireDao;

@Repository
public class QuestionnaireDaoImpl implements QuestionnaireDao{

	@Autowired
	private CommonDao dao;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public Map<String, Object> getPageData(Map<String, Object> param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		list = listPageData(param);
		count = listCount(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public Map<String, Object> getPageDataSt(Map<String, Object> param) {
		String count = "0";
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		
		list = listPageDataSt(param);
		count = listCountSt(param);
		
		Map<String, Object> rs = new HashMap<String, Object>();
		rs.put("totalCount", count);
		rs.put("pageData", list);
		return rs;
	}
	
	@Override
	public void questionnaireOper(List<Map<String, Object>> list) {
		String sql = "insert into t_questionnaire(tradeName,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12,question13,question14,question15,question16,question17,question18,question19,question20,question21,question22,question23,advise,companyName,personalName,phone,daytime,yesNum,noNum) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "tradeName,question2,question3,question4,question5,question6,question7,question8,question9,question10,question11,question12,question13,question14,question15,question16,question17,question18,question19,question20,question21,question22,question23,advise,companyName,personalName,phone,daytime,yesNum,noNum";
		dao.insertBatchTableData(list, sql, columns,500);
	}

	@Override
	public List<Map<String, Object>> questionnaireOperCheck(String companyName,String tradeName) {
		String sql = "select companyName,tradeName from t_questionnaire where companyName='"+companyName+"' and tradeName='"+tradeName+"'";
		return jdbcTemplate.queryForList(sql);
	}
	
	public List<Map<String, Object>> listPageData(Map<String, Object> param){
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from t_questionnaire where 1 = 1");
		sql.append(getQueryConf(param));
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String listCount(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		
		sql.append("select count(*) count from t_questionnaire where 1 = 1");
		sql.append(getQueryConf(param));
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public List<Map<String, Object>> listPageDataSt(Map<String, Object> param){
		Page page = (Page) param.get("page");
		int offset = (page.getPageNo() - 1) * page.getPageSize();
		StringBuffer sql = new StringBuffer();
		sql.append("select tradeName,sum(yesNum) as yesNum,sum(noNum) as noNum,count(*) companyNum from t_questionnaire where 1 = 1 ");
		sql.append(getQueryConfSt(param));
		sql.append(" group by tradeName");
		sql.append(" limit " + page.getPageSize() + " offset " + offset+ " ;");
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String listCountSt(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(1) count from(select 1 from t_questionnaire where 1 = 1 ");
		sql.append(getQueryConfSt(param));
		sql.append("group by tradeName) t");
		
		Map<String, Object> map = jdbcTemplate.queryForList(sql.toString()).get(0);
		String count = map.get("count").toString();
		return count;
	}
	
	public StringBuffer getQueryConf(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		String tradeName = CommonUtils.valueOf(param.get("tradeName"));
		String companyName = CommonUtils.valueOf(param.get("companyName"));
		
		if(StringUtils.isNotBlank(tradeName)) {
			sql.append(" and tradeName like '%"+tradeName+"%'");
		}
		if(StringUtils.isNotBlank(companyName)) {
			sql.append(" and companyName like '%"+companyName+"%'");
		}
		return sql;
	}
	
	public StringBuffer getQueryConfSt(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		String tradeName = CommonUtils.valueOf(param.get("tradeName"));
		
		if(StringUtils.isNotBlank(tradeName)) {
			sql.append(" and tradeName like '%"+tradeName+"%'");
		}
		return sql;
	}
}